﻿using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;
using System;
using System.Collections.Specialized;
using System.Collections.Generic;

namespace MarginTrading.Services
{
    public class DailyInterestServices
    {
        private static DailyInterest GetDailyInterestFromDataReader(IDataReader dataReader)
        {
            DailyInterest retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new DailyInterest();
                retVal.ClientCode = dataReader["CLIENT_CODE"].ToString();
                double interest = 0;
                if (double.TryParse(dataReader["INTEREST"].ToString(), out interest))
                    retVal.Interest = interest;
                DateTime interestDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["INTEREST_DATE"].ToString(), out interestDate))
                    retVal.InterestDate = interestDate;

                int AccPost = 0;
                if (int.TryParse(dataReader["ACC_POSTED"].ToString(), out AccPost))
                    retVal.AccPosted = AccPost;

            }
            return retVal;
        }

        public static DailyInterest GetDailyInterestByClientCodeAndInterestDate(string clintCode, DateTime interestDate)
        {
            DailyInterest retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_INTEREST where CLIENT_CODE = :v1 and INTEREST_DATE = :v2";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clintCode;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = interestDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetDailyInterestFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DailyInterestCollection GetDailyInterestCollection()
        {
            DailyInterestCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_INTEREST";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DailyInterestCollection();
                    while (dataReader.Read())
                    {
                        DailyInterest item = GetDailyInterestFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetDailyInterestCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DAILY_INTEREST";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(DailyInterest dailyInterest)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into DAILY_INTEREST(CLIENT_CODE,INTEREST,INTEREST_DATE,ACC_POSTED)" +
                                  "values(:v1,:v2,:v3,:v4)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dailyInterest.ClientCode;
                cmd.Parameters.Add(":v2", OracleType.Number).Value = dailyInterest.Interest;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = dailyInterest.InterestDate;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = dailyInterest.AccPosted;


                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(DailyInterest InterestObj)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from DAILY_INTEREST where CLIENT_CODE = :v1 and INTEREST_DATE = :v2";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = InterestObj.ClientCode;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = InterestObj.InterestDate;
                

                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(DailyInterest dailyInterest)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update DAILY_INTEREST set " +
                                  "INTEREST = :v2, ACC_POSTED = :v4 " +
                                  "where CLIENT_CODE = :v1 and INTEREST_DATE = :v3";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dailyInterest.ClientCode;
                cmd.Parameters.Add(":v2", OracleType.Number).Value = dailyInterest.Interest;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = dailyInterest.InterestDate;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = dailyInterest.AccPosted;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }



        public static void UpdateStatus(string ClientCode, DateTime StartDate, DateTime EndDate, int Status) //Ravn
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update DAILY_INTEREST set " +
                                  "ACC_POSTED = :v2 " +
                                  "where CLIENT_CODE = :v1 and "+
                "interest_date >= to_date('" + StartDate.ToString("MM/dd/yyyy") + "', 'MM/dd/yyyy')  and " +
                "interest_date <= to_date('" + EndDate.ToString("MM/dd/yyyy") + "', 'MM/dd/yyyy') and ACC_POSTED = 0"; 

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = ClientCode;
                cmd.Parameters.Add(":v2", OracleType.Number).Value = Status;
                //cmd.Parameters.Add(":v3", OracleType.DateTime).Value = StartDate;
                //cmd.Parameters.Add(":v4", OracleType.DateTime).Value = EndDate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();
                

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }



        public static String GetRecentDate() //Ravin
        {
            String retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select max(interest_date) from daily_interest";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = dataReader[0].ToString();
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static Dictionary<String, double> GetCumInterest(DateTime StartDate, DateTime EndDate)//Ravin
        {
            Dictionary<String, double> CumInterest = new Dictionary<String, double>();
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                //cmd.CommandText = "select * from DAILY_INTEREST where CLIENT_CODE = :v1 and INTEREST_DATE = :v2";
                cmd.CommandText =   "select client_code, sum(interest) from daily_interest "+
                                     "where acc_posted = 0 and " +
                                     "interest_date >= to_date('" + StartDate.ToString("MM/dd/yyyy") + "', 'MM/dd/yyyy')  and " +
                                     "interest_date <= to_date('" + EndDate.ToString("MM/dd/yyyy") + "', 'MM/dd/yyyy')  " +
                                     "group by client_code";
                
                //cmd.Parameters.Add(":v1", OracleType.DateTime).Value = StartDate;
                //cmd.Parameters.Add(":v2", OracleType.DateTime).Value = EndDate;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {

                    while (dataReader.Read())
                    {
                        if (!string.IsNullOrEmpty(dataReader[0].ToString()) &&
                                !string.IsNullOrEmpty(dataReader[1].ToString()))
                            CumInterest.Add(dataReader[0].ToString(), double.Parse(dataReader[1].ToString()));
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return CumInterest;
        }


        public static double GetDailyInterestByClientCode(string clientCode)
        {
            double fRetVal = 0.0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select sum(interest) from DAILY_INTEREST where CLIENT_CODE = :v1 and acc_posted = 0";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientCode;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double fAmount = 0;
                        if (double.TryParse(dataReader[0].ToString(), out fAmount))
                        {
                            fRetVal = fAmount;
                        }
                        else
                        {
                            fRetVal = 0;
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return fRetVal;
        }


        public static double GetInterestIncome(DateTime EndDate, String Agent)
        {
            double fRetVal = 0.0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select sum(i.interest) from DAILY_INTEREST i, client c where (i.acc_posted = 0 or i.acc_posted = 1) " +
                    "and i.interest_date < (to_date('" + EndDate.ToString("MM/dd/yyyy") + "', 'MM/dd/yyyy')+1) and c.code = i.client_code";

                //cmd.Parameters.Add(":v1", OracleType.VarChar).Value = Agent;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double fAmount = 0;
                        if (double.TryParse(dataReader[0].ToString(), out fAmount))
                        {
                            fRetVal = fAmount;
                        }
                        else
                        {
                            fRetVal = 0;
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return fRetVal;
        }

        public static double GetAccruedInterest(DateTime EndDate, String Agent)
        {
            double fRetVal = 0.0;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select sum(i.interest) from DAILY_INTEREST i, client c where i.acc_posted = 0 and " +
                    " i.interest_date < (to_date('" + EndDate.ToString("MM/dd/yyyy") + "', 'MM/dd/yyyy')+1) and c.code = i.client_code";

                //cmd.Parameters.Add(":v1", OracleType.VarChar).Value = Agent;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        double fAmount = 0;
                        if (double.TryParse(dataReader[0].ToString(), out fAmount))
                        {
                            fRetVal = fAmount;
                        }
                        else
                        {
                            fRetVal = 0;
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return fRetVal;
        }


    }
}
